Spatial data everywhere!

Adding geo dimension

Geographical data is everywhere. In Statoil we use maps to keep track of the licenses, well/installation locations, we use maps to outline the prospects and plan oil/gas pipelines. Our internal ArcGIS database contains a lot of spatial data produced internally (shapefiles for basins, licenses, prospects/fields), as well as procured from external sources (IHS, Tellus, NPD, etc).

However you may still find yourself feeling you are “starting from scratch”. The truth is, you probably have geo data even if you don’t have column for longitude and latitude. These days, google knows everything, and if google knows it, you probably have coordinates.

NB! Google allows only 2500 requests per day through their API free of charge

library(ggmap)
library(dplyr)
statoil_locations <- c("statoil fornebu", "statoil forus", "statoil kontor stjoerdal", "statoil harstad", "statoil tjeldbergodden", "statoil mongstad", "statoil kaarstoe", "statoil melkoeya") 
stl_loc_df <- statoil_locations %>% 
  geocode() %>% bind_cols(location=statoil_locations)
Information from URL : http://maps.googleapis.com/maps/api/geocode/json?address=statoil%20fornebu&sensor=false
Information from URL : http://maps.googleapis.com/maps/api/geocode/json?address=statoil%20forus&sensor=false
.Information from URL : http://maps.googleapis.com/maps/api/geocode/json?address=statoil%20kontor%20stjoerdal&sensor=false
.Information from URL : http://maps.googleapis.com/maps/api/geocode/json?address=statoil%20harstad&sensor=false
geocode failed with status OVER_QUERY_LIMIT, location = "statoil harstad".Information from URL : http://maps.googleapis.com/maps/api/geocode/json?address=statoil%20tjeldbergodden&sensor=false
.Information from URL : http://maps.googleapis.com/maps/api/geocode/json?address=statoil%20mongstad&sensor=false
geocode failed with status OVER_QUERY_LIMIT, location = "statoil mongstad".Information from URL : http://maps.googleapis.com/maps/api/geocode/json?address=statoil%20kaarstoe&sensor=false
.Information from URL : http://maps.googleapis.com/maps/api/geocode/json?address=statoil%20melkoeya&sensor=false
stl_loc_df

Let’s see if we can plot these:

library(leaflet)
p <- leaflet(stl_loc_df) %>% 
  addTiles() %>% 
  addMarkers(popup = ~location) 
Assuming 'lon' and 'lat' are longitude and latitude, respectively
Data contains 2 rows with either missing or invalid lat/lon values and will be ignored
p

Not bad! Lets see if we can get some shapefiles to play with:

library(raster)
Loading required package: sp

Attaching package: 㤼㸱raster㤼㸲

The following object is masked from 㤼㸱package:dplyr㤼㸲:

    select
norway_fylke <- getData('GADM',
  country = 'NO', 
  level = 1) 
p %>% 
  addPolygons(data = norway_fylke)

Of course you can read shapefiles (e.g. Statoil/Tellus basin outlines) and data frames with pre-populated coordinates (e.g. well locations in Woodmac Upstream Data Tool).

Manipulating spatial data

Now that we have spatial data, how do we manipulate it? For example, how do we find out, in which fylke is each of our offices located?

First we will need to transform our data into the “tidy” format (encode geo-data as as simple feature and include it into the “rectangular” dataframe as special “geometry” column). Once the data is in the “tidy” format, we can easily make “spatial join” using familiar “tidyverse” tools.

Please, review awesome vignettes to sf package

library(sf)
stl_loc_geo <- stl_loc_df %>% na.omit() %>% st_as_sf(coords=c("lon", "lat"), crs=4326)
stl_loc_geo
Simple feature collection with 6 features and 1 field
geometry type:  POINT
dimension:      XY
bbox:           xmin: 5.523974 ymin: 58.89266 xmax: 23.60045 ymax: 70.68937
epsg (SRID):    4326
proj4string:    +proj=longlat +datum=WGS84 +no_defs
                  location                      geometry
1          statoil fornebu POINT (10.6300284 59.8958549)
2            statoil forus  POINT (5.7190168 58.8926611)
3 statoil kontor stjoerdal POINT (10.9074387 63.4701076)
5   statoil tjeldbergodden  POINT (8.6937046 63.4084316)
7         statoil kaarstoe  POINT (5.5239741 59.2789269)
8         statoil melkoeya  POINT (23.600448 70.6893659)
norway_fylke_geo <- norway_fylke %>% st_as_sf(crs=4326)
stl_loc_geo %>% 
  st_join(norway_fylke_geo, join=st_within) %>% 
  as_tibble() %>% 
  dplyr::select(location, NAME_1)
although coordinates are longitude/latitude, it is assumed that they are planar

What if we want to take geo data from the user, say I want to mark up my favorite hiking areas? (or indicate boundaries of a new prospect or a prospective area to be allocated in the new licensing round).

hiking_areas_geo
Simple feature collection with 3 features and 2 fields
geometry type:  POLYGON
dimension:      XY
bbox:           xmin: 8.9648 ymin: 59.9715 xmax: 10.7364 ymax: 63.4352
epsg (SRID):    4326
proj4string:    +proj=longlat +datum=WGS84 +no_defs
  X_leaflet_id feature_type                       geometry
1           81      polygon POLYGON ((10.676 60.0127, 1...
2           94      polygon POLYGON ((9.1406 60.8449, 8...
3          107      polygon POLYGON ((10.2571 63.4302, ...

Ok. Where are those located? Or rather, give me the list of hikeable fylkes (fylke that contain my hiking spots). Note that I marked the areas without looking at the fylke borders so I am interested in a generic join of polygons that may be intersecting.

hiking_areas_geo %>% 
  st_join(norway_fylke_geo) %>% 
  as_tibble() %>% 
  dplyr::select(NAME_1) %>% 
  distinct
although coordinates are longitude/latitude, it is assumed that they are planar

Finally, last summer, my friend Adrian took me fishing outside of Linnesøya in Trøndelag. We had fancy fishing equipment and a sonar device in the boat, so we were able to get a lot of fish, but I always wondered how deep was the water in that area and whether I could go with my regual fishing rod to catch that steinbit that cut off my lure.

cent
         X        Y
1 10.24712 64.41337

so what was the waterdepth in the area we were fishing (using centroid location)? How far was it from shore? What does the seabed look like? First, we fetch bathymetry data from free online service to explore seabed in this location

LS0tDQp0aXRsZTogIlItU3BhdGlhbCBOb3RlYm9vayINCm91dHB1dDogaHRtbF9ub3RlYm9vaw0KLS0tDQoNCiMjIFNwYXRpYWwgZGF0YSBldmVyeXdoZXJlIQ0KDQojIyMgQWRkaW5nIGdlbyBkaW1lbnNpb24NCg0KR2VvZ3JhcGhpY2FsIGRhdGEgaXMgZXZlcnl3aGVyZS4gSW4gU3RhdG9pbCB3ZSB1c2UgbWFwcyB0byBrZWVwIHRyYWNrIG9mIHRoZSBsaWNlbnNlcywgd2VsbC9pbnN0YWxsYXRpb24gbG9jYXRpb25zLCB3ZSB1c2UgbWFwcyB0byBvdXRsaW5lIHRoZSBwcm9zcGVjdHMgYW5kIHBsYW4gb2lsL2dhcyBwaXBlbGluZXMuIE91ciBpbnRlcm5hbCBBcmNHSVMgZGF0YWJhc2UgY29udGFpbnMgYSBsb3Qgb2Ygc3BhdGlhbCBkYXRhIHByb2R1Y2VkIGludGVybmFsbHkgKHNoYXBlZmlsZXMgZm9yIGJhc2lucywgbGljZW5zZXMsIHByb3NwZWN0cy9maWVsZHMpLCBhcyB3ZWxsIGFzIHByb2N1cmVkIGZyb20gZXh0ZXJuYWwgc291cmNlcyAoSUhTLCBUZWxsdXMsIE5QRCwgZXRjKS4NCg0KSG93ZXZlciB5b3UgbWF5IHN0aWxsIGZpbmQgeW91cnNlbGYgZmVlbGluZyB5b3UgYXJlICJzdGFydGluZyBmcm9tIHNjcmF0Y2giLiBUaGUgdHJ1dGggaXMsIHlvdSBwcm9iYWJseSBoYXZlIGdlbyBkYXRhIGV2ZW4gaWYgeW91IGRvbid0IGhhdmUgY29sdW1uIGZvciBsb25naXR1ZGUgYW5kIGxhdGl0dWRlLiBUaGVzZSBkYXlzLCBnb29nbGUga25vd3MgZXZlcnl0aGluZywgYW5kIGlmIGdvb2dsZSBrbm93cyBpdCwgeW91IHByb2JhYmx5IGhhdmUgY29vcmRpbmF0ZXMuIA0KDQo+IE5CISBHb29nbGUgYWxsb3dzIG9ubHkgMjUwMCByZXF1ZXN0cyBwZXIgZGF5IHRocm91Z2ggdGhlaXIgQVBJIGZyZWUgb2YgY2hhcmdlDQoNCmBgYHtyfQ0KbGlicmFyeShnZ21hcCkNCmxpYnJhcnkoZHBseXIpDQpzdGF0b2lsX2xvY2F0aW9ucyA8LSBjKCJzdGF0b2lsIGZvcm5lYnUiLCAic3RhdG9pbCBmb3J1cyIsICJzdGF0b2lsIGtvbnRvciBzdGpvZXJkYWwiLCAic3RhdG9pbCBoYXJzdGFkIiwgInN0YXRvaWwgdGplbGRiZXJnb2RkZW4iLCAic3RhdG9pbCBtb25nc3RhZCIsICJzdGF0b2lsIGthYXJzdG9lIiwgInN0YXRvaWwgbWVsa29leWEiKSANCg0Kc3RsX2xvY19kZiA8LSBzdGF0b2lsX2xvY2F0aW9ucyAlPiUgDQogIGdlb2NvZGUoKSAlPiUgYmluZF9jb2xzKGxvY2F0aW9uPXN0YXRvaWxfbG9jYXRpb25zKQ0KDQpzdGxfbG9jX2RmDQoNCmBgYA0KDQpMZXQncyBzZWUgaWYgd2UgY2FuIHBsb3QgdGhlc2U6DQoNCmBgYHtyfQ0KbGlicmFyeShsZWFmbGV0KQ0KDQpwIDwtIGxlYWZsZXQoc3RsX2xvY19kZikgJT4lIA0KICBhZGRUaWxlcygpICU+JSANCiAgYWRkTWFya2Vycyhwb3B1cCA9IH5sb2NhdGlvbikgDQoNCnANCmBgYA0KDQpOb3QgYmFkISBMZXRzIHNlZSBpZiB3ZSBjYW4gZ2V0IHNvbWUgc2hhcGVmaWxlcyB0byBwbGF5IHdpdGg6DQoNCmBgYHtyfQ0KbGlicmFyeShyYXN0ZXIpDQoNCm5vcndheV9meWxrZSA8LSBnZXREYXRhKCdHQURNJywNCiAgY291bnRyeSA9ICdOTycsIA0KICBsZXZlbCA9IDEpIA0KDQpwICU+JSANCiAgYWRkUG9seWdvbnMoZGF0YSA9IG5vcndheV9meWxrZSkNCg0KYGBgDQoNCk9mIGNvdXJzZSB5b3UgY2FuIHJlYWQgc2hhcGVmaWxlcyAoZS5nLiBTdGF0b2lsL1RlbGx1cyBiYXNpbiBvdXRsaW5lcykgYW5kIGRhdGEgZnJhbWVzIHdpdGggcHJlLXBvcHVsYXRlZCBjb29yZGluYXRlcyAoZS5nLiB3ZWxsIGxvY2F0aW9ucyBpbiBXb29kbWFjIFVwc3RyZWFtIERhdGEgVG9vbCkuDQoNCiMjIyBNYW5pcHVsYXRpbmcgc3BhdGlhbCBkYXRhDQoNCk5vdyB0aGF0IHdlIGhhdmUgc3BhdGlhbCBkYXRhLCBob3cgZG8gd2UgbWFuaXB1bGF0ZSBpdD8gRm9yIGV4YW1wbGUsIGhvdyBkbyB3ZSBmaW5kIG91dCwgaW4gd2hpY2ggZnlsa2UgaXMgZWFjaCBvZiBvdXIgb2ZmaWNlcyBsb2NhdGVkPyANCg0KRmlyc3Qgd2Ugd2lsbCBuZWVkIHRvIHRyYW5zZm9ybSBvdXIgZGF0YSBpbnRvIHRoZSAidGlkeSIgZm9ybWF0IChlbmNvZGUgZ2VvLWRhdGEgYXMgYXMgc2ltcGxlIGZlYXR1cmUgYW5kIGluY2x1ZGUgaXQgaW50byB0aGUgInJlY3Rhbmd1bGFyIiBkYXRhZnJhbWUgYXMgc3BlY2lhbCAiZ2VvbWV0cnkiIGNvbHVtbikuIE9uY2UgdGhlIGRhdGEgaXMgaW4gdGhlICJ0aWR5IiBmb3JtYXQsIHdlIGNhbiBlYXNpbHkgbWFrZSAic3BhdGlhbCBqb2luIiB1c2luZyBmYW1pbGlhciAidGlkeXZlcnNlIiB0b29scy4NCg0KPiBQbGVhc2UsIHJldmlldyBhd2Vzb21lIHZpZ25ldHRlcyB0byBgc2ZgIHBhY2thZ2UNCg0KYGBge3J9DQpsaWJyYXJ5KHNmKQ0KDQpzdGxfbG9jX2dlbyA8LSBzdGxfbG9jX2RmICU+JSBuYS5vbWl0KCkgJT4lIHN0X2FzX3NmKGNvb3Jkcz1jKCJsb24iLCAibGF0IiksIGNycz00MzI2KQ0Kc3RsX2xvY19nZW8NCg0Kbm9yd2F5X2Z5bGtlX2dlbyA8LSBub3J3YXlfZnlsa2UgJT4lIHN0X2FzX3NmKGNycz00MzI2KQ0KDQpzdGxfbG9jX2dlbyAlPiUgDQogIHN0X2pvaW4obm9yd2F5X2Z5bGtlX2dlbywgam9pbj1zdF93aXRoaW4pICU+JSANCiAgYXNfdGliYmxlKCkgJT4lIA0KICBkcGx5cjo6c2VsZWN0KGxvY2F0aW9uLCBOQU1FXzEpDQoNCmBgYA0KDQpXaGF0IGlmIHdlIHdhbnQgdG8gdGFrZSBnZW8gZGF0YSBmcm9tIHRoZSB1c2VyLCBzYXkgSSB3YW50IHRvIG1hcmsgdXAgbXkgZmF2b3JpdGUgaGlraW5nIGFyZWFzPyAob3IgaW5kaWNhdGUgYm91bmRhcmllcyBvZiBhIG5ldyBwcm9zcGVjdCBvciBhIHByb3NwZWN0aXZlIGFyZWEgdG8gYmUgYWxsb2NhdGVkIGluIHRoZSBuZXcgbGljZW5zaW5nIHJvdW5kKS4NCg0KYGBge3J9DQojaW5zdGFsbC5wYWNrYWdlcygibWFwZWRpdCIpDQpsaWJyYXJ5KG1hcGVkaXQpDQpsaWJyYXJ5KG1hcHZpZXcpDQoNCmhpa2luZ19hcmVhcyA8LSBtYXB2aWV3KCkgJT4lIGVkaXRNYXAoKQ0KDQpoaWtpbmdfYXJlYXNfZ2VvIDwtIGhpa2luZ19hcmVhcyRmaW5pc2hlZA0KDQpgYGANCg0KT2suIFdoZXJlIGFyZSB0aG9zZSBsb2NhdGVkPyBPciByYXRoZXIsIGdpdmUgbWUgdGhlIGxpc3Qgb2YgaGlrZWFibGUgZnlsa2VzIChmeWxrZSB0aGF0IGNvbnRhaW4gbXkgaGlraW5nIHNwb3RzKS4gTm90ZSB0aGF0IEkgbWFya2VkIHRoZSBhcmVhcyB3aXRob3V0IGxvb2tpbmcgYXQgdGhlIGZ5bGtlIGJvcmRlcnMgc28gSSBhbSBpbnRlcmVzdGVkIGluIGEgZ2VuZXJpYyBqb2luIG9mIHBvbHlnb25zIHRoYXQgbWF5IGJlIGludGVyc2VjdGluZy4NCg0KYGBge3J9DQpoaWtpbmdfYXJlYXNfZ2VvICU+JSANCiAgc3Rfam9pbihub3J3YXlfZnlsa2VfZ2VvKSAlPiUgDQogIGFzX3RpYmJsZSgpICU+JSANCiAgZHBseXI6OnNlbGVjdChOQU1FXzEpICU+JSANCiAgZGlzdGluY3QNCmBgYA0KDQpGaW5hbGx5LCBsYXN0IHN1bW1lciwgbXkgZnJpZW5kIEFkcmlhbiB0b29rIG1lIGZpc2hpbmcgb3V0c2lkZSBvZiBMaW5uZXPDuHlhIGluIFRyw7huZGVsYWcuIFdlIGhhZCBmYW5jeSBmaXNoaW5nIGVxdWlwbWVudCBhbmQgYSBzb25hciBkZXZpY2UgaW4gdGhlIGJvYXQsIHNvIHdlIHdlcmUgYWJsZSB0byBnZXQgYSBsb3Qgb2YgZmlzaCwgYnV0IEkgYWx3YXlzIHdvbmRlcmVkIGhvdyBkZWVwIHdhcyB0aGUgd2F0ZXIgaW4gdGhhdCBhcmVhIGFuZCB3aGV0aGVyIEkgY291bGQgZ28gd2l0aCBteSByZWd1YWwgZmlzaGluZyByb2QgdG8gY2F0Y2ggdGhhdCBzdGVpbmJpdCB0aGF0IGN1dCBvZmYgbXkgbHVyZS4NCg0KYGBge3J9DQpmaXNoaW5nX2FyZWFfYW5kX2hvdXNlIDwtIG5vcndheV9meWxrZV9nZW8gJT4lIA0KICBmaWx0ZXIoTkFNRV8xPT0iU8O4ci1UcsO4bmRlbGFnIikgJT4lIA0KICBtYXB2aWV3KCkgJT4lIA0KICBlZGl0TWFwKCkNCg0KZmlzaGluZ19hcmVhX2FuZF9ob3VzZSRmaW5pc2hlZA0KDQpjZW50IDwtIGZpc2hpbmdfYXJlYV9hbmRfaG91c2UkZmluaXNoZWQgJT4lIA0KICBmaWx0ZXIoZmVhdHVyZV90eXBlPT0icG9seWdvbiIpICU+JSANCiAgc3RfY2VudHJvaWQoKSAlPiUgDQogIHN0X2Nvb3JkaW5hdGVzKCkgDQoNCmhvbWUgPC0gZmlzaGluZ19hcmVhX2FuZF9ob3VzZSRmaW5pc2hlZCAlPiUgDQogIGZpbHRlcihmZWF0dXJlX3R5cGU9PSJtYXJrZXIiKSAlPiUgDQogIHN0X2Nvb3JkaW5hdGVzKCkNCg0KDQpgYGANCg0Kc28gd2hhdCB3YXMgdGhlIHdhdGVyZGVwdGggaW4gdGhlIGFyZWEgd2Ugd2VyZSBmaXNoaW5nICh1c2luZyBjZW50cm9pZCBsb2NhdGlvbik/IEhvdyBmYXIgd2FzIGl0IGZyb20gc2hvcmU/IFdoYXQgZG9lcyB0aGUgc2VhYmVkIGxvb2sgbGlrZT8NCkZpcnN0LCB3ZSBmZXRjaCBiYXRoeW1ldHJ5IGRhdGEgZnJvbSBmcmVlIG9ubGluZSBzZXJ2aWNlIHRvIGV4cGxvcmUgc2VhYmVkIGluIHRoaXMgbG9jYXRpb24NCg0KYGBge3J9DQojIGluc3RhbGwucGFja2FnZXMoIm1hcm1hcCIpDQpsaWJyYXJ5KG1hcm1hcCkNCmxpYnJhcnkocHVycnIpDQoNCmZpc2hpbmdfcmVnaW9uIDwtIGZpc2hpbmdfYXJlYV9hbmRfaG91c2UkZmluaXNoZWQgJT4lIA0KICBzdF9iYm94KCkgJT4lIA0KICBzZXROYW1lcyhjKCJsb24xIiwgImxhdDEiLCAibG9uMiIsICJsYXQyIikpICU+JSANCiAgYyhyZXNvbHV0aW9uPTEpICU+JSANCiAgYXMubGlzdCgpDQoNCmJhdGh5X21hdHJpeDwtaW52b2tlKGdldE5PQUEuYmF0aHksIGZpc2hpbmdfcmVnaW9uKQ0KDQojIHdhdGVyIGRlcHRoDQp3X2RlcHRoIDwtIGdldC5kZXB0aChiYXRoeV9tYXRyaXgsIGNlbnQsIGxvY2F0b3IgPSBGQUxTRSkkZGVwdGgNCg0KcGxvdChiYXRoeV9tYXRyaXgsIGltYWdlPVRSVUUsIGx3ZCA9IDAuMSwgbWFpbj0ibXkgZmlzaGluZyBzcG90IikNCnBvaW50cyhjZW50LCBwY2g9MTksIGNvbD0icmVkIikNCnRleHQoY2VudCwgcGFzdGUoIlxuRGVwdGg6Iiwgd19kZXB0aCwgIm0iKSwgY29sID0gIndoaXRlIiwgZm9udCA9IDMpDQpwb2ludHMoaG9tZSwgcGNoPTE5LCBjb2w9ImdyZWVuIikNCnRleHQoaG9tZSwgIlxuSG9tZSIsIGNvbCA9ICJncmVlbiIsIGZvbnQgPSAzKQ0KDQpmaXNoaW5nX2FyZWFfYW5kX2hvdXNlJGZpbmlzaGVkICU+JQ0KICBmaWx0ZXIoZmVhdHVyZV90eXBlPT0icG9seWdvbiIpICU+JSANCiAgc3RfZ2VvbWV0cnkoKSAlPiUgYXNfU3BhdGlhbCgpICU+JSANCiAgcGxvdChhZGQ9VFJVRSwgYm9yZGVyPSJ5ZWxsb3ciKQ0KDQoNCmBgYA0KDQoNCg0KYGBge3J9DQoNCiMgZGlzdGFuY2UgdG8gc2hvcmUNCmRpc3RfdG9fc2hvcmUgPC0gZGlzdDJpc29iYXRoKGJhdGh5X21hdHJpeCwgeD1yYmluZChjZW50LCBjZW50KSwgaXNvYmF0aCA9IDApWzEsXQ0KZGlzdF90b19zaG9yZQ0KDQojIHdoYXQgaXMgdGhlIHNlYWJlZCBsaWtlIGZyb20gc2hvcmUgdG8gd2hlcmUgd2Ugd2VyZSBmaXNoaW5nPw0KZ2V0LnRyYW5zZWN0KG1hdCA9IGJhdGh5X21hdHJpeCwgDQogICAgICAgICAgICAgeDI9IGRpc3RfdG9fc2hvcmUkc3RhcnQubG9uLCANCiAgICAgICAgICAgICB5MiA9IGRpc3RfdG9fc2hvcmUkc3RhcnQubGF0LCANCiAgICAgICAgICAgICB4MSA9IGRpc3RfdG9fc2hvcmUkZW5kLmxvbiwgDQogICAgICAgICAgICAgeTEgPSBkaXN0X3RvX3Nob3JlJGVuZC5sYXQsIA0KICAgICAgICAgICAgIGRpc3RhbmNlID0gVFJVRSkgJT4lIA0KICBwbG90UHJvZmlsZSgpDQoNCiMgZ2V0IGEgY2xvc2VyIGxvb2sgYXQgdGhlIHNlYWJlZA0KbGlicmFyeShsYXR0aWNlKQ0Kd2lyZWZyYW1lKHVuY2xhc3MoYmF0aHlfbWF0cml4KSwgc2hhZGU9VFJVRSwgYXNwZWN0ID0gYygxLCAwLjUpKQ0KYGBgDQoNCg==